<%@ page language="java" errorPage="../../error/exceptionHandler.jsp" %>
<%@ page import="java.sql.*" %>
<%@ page import="org.apache.log4j.Logger" %>
<%@ page import="org.cgs.web.database.DatabaseConnector" %>

<%
	Logger LOGGER = Logger.getLogger("studentMain.jsp");
	String courseCode = (String) request.getSession().getAttribute("courseCode");
%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en-AU">
  <head>
    <meta http-equiv="content-type" content="application/xhtml+xml; charset=UTF-8" />
    <link rel="stylesheet" type="text/css" href="../../css/sinorca-screen.css" media="screen" title="Sinorca (screen)" />
    <link rel="stylesheet alternative" type="text/css" href="../../css/sinorca-screen-alt.css" media="screen" title="Sinorca (alternative)" />
    <link rel="stylesheet" type="text/css" href="../../css/sinorca-print.css" media="print" />

    <title>Instructor Students Page</title>
  </head>

  <body>
    <div id="top"><a href="#main-copy" class="doNotDisplay doNotPrint">Skip to main content.</a></div>
    <div id="header">
      <div class="superHeader">
      </div>

      <div class="midHeader">
        <h1 class="headerTitle">Course Group Scheduler 3.0</h1>
      </div>
    </div>

    <!-- ##### Side Bar ##### -->
    <div id="side-bar">
      <div>
        <p class="sideBarTitle">Instructor Menu</p>
        <ul>
	  <li><a href="../instructorMain.jsp">Home</a></li>
	  <li><a href="../settings/changePassword.jsp">Change Password</a></li>
	  <li><a href="../../logout.jsp">Logout</a></li>
        </ul>
      </div>

      <div>
        <p class="sideBarTitle">Students Menu</p>
        <ul>
	  <li><a href="addStudent.jsp">Add Students</a></li>
	  <li><a href="viewStudents.jsp">View Students</a></li>
        </ul>
      </div>

      <div>
        <p class="sideBarTitle">Schools Menu</p>
        <ul>
	  <li><a href="../schools/addSchool.jsp">Add Schools</a></li>
	  <li><a href="../schools/viewSchools.jsp">View Schools</a></li>
        </ul>
      </div>

      <div>
        <p class="sideBarTitle">Scheduler</p>
        <ul>
          <li><a href="../scheduler/scheduler.jsp">Run Scheduler</a></li>
        </ul>
      </div>
    </div>

    <!-- ##### Main Copy ##### -->

    <div id="main-copy">
      <h1 id="introduction">Student Data</h1>
      <p>The following students are registerd and/or submitted data for course <%= courseCode %></p>
      <form name="f" method="POST" action="student.jsp">
      <table>
	      <tr><b><td>Name</td><td>Student Number</td><td>Email</td><td>Phone</td><td>Vehicle</td><td>Teaching Experience</td><td>Subject Knowledge</td></b></tr>
	  <%
	     Connection con = null;
	     PreparedStatement pstmt = null;
	     ResultSet rs = null;

	     try
	     {
                con = DatabaseConnector.getInstance().getConnection();
	        pstmt = con.prepareStatement("SELECT * FROM student WHERE courseCode=? ORDER BY name");
	        pstmt.setString(1, courseCode);
	        rs = pstmt.executeQuery();

	     	while(rs.next())
		{
		String name = rs.getString("name");
		String studentNumber = rs.getString("studentNumber");
		String email = rs.getString("email");
		String phone = rs.getString("phone");
		String hasVehicle = rs.getString("hasVehicle");
		if(hasVehicle == null || hasVehicle.length() == 0)
		{
		    hasVehicle = "N/A";
		}
		String hasTeachingExperience = rs.getString("hasTeachingExperience");
		if(hasTeachingExperience == null || hasTeachingExperience.length() == 0)
		{
		    hasTeachingExperience = "N/A";
		}
		String hasSubjectKnowledge = rs.getString("hasSUbjectKnowledge");
		if(hasSubjectKnowledge == null || hasSubjectKnowledge.length() == 0)
		{
		    hasSubjectKnowledge = "N/A";
		}
 	%>
	
	<tr><td><a href="student.jsp?id=<%=studentNumber%>&courseCode=<%=courseCode%>"%><%= name %></a></td><td><%= studentNumber %></td><td><%= email %></td><td><%= phone %></td><td><%= hasVehicle %></td><td><%= hasTeachingExperience %></td><td><%= hasSubjectKnowledge %></td></tr>
        <%
	       } 
	    }
	    catch(Exception e)
	    {
                LOGGER.error("Exception thrown while atttempting to delete schools from DB", e);
	    }
	    finally
	    {
            try
            {
                // Close the ResultSet
                if (rs != null)
                {
                    rs.close();
                }
            }
            catch (Exception e)
            {
                LOGGER.error("Exception cleaning up result set", e);
            }

            try
            {
                // Close the PreparedStatement
                if (pstmt != null)
                {
                    pstmt.close();
                }
            }
            catch (Exception e)
            {
                LOGGER.error("Exception cleaning up prepared statement", e);
            }

            try
            {
                // Close the connection
                if (con != null)
                {
                    con.close();
                }
            }
            catch (Exception e)
            {
                LOGGER.error("Exception cleaning up connection to the database", e);
            }
	    }
	%>
      </table>
      </form>
      <a class="topOfPage" href="#top" title="Go to the top of this page">^ TOP</a>
    
    <!-- ##### Footer ##### -->
    <div id="footer">
        E-mail:&nbsp;<a href="./index.html" title="Email webmaster">jeffrey.s.garrett@gmail.com</a>
    </div>
  </body>
</html>
